/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50141
Source Host           : localhost:3306
Source Database       : sport

Target Server Type    : MYSQL
Target Server Version : 50141
File Encoding         : 65001

Date: 2014-02-17 14:13:02
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `users`
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`userid`  int(11) NOT NULL AUTO_INCREMENT ,
`username`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`password`  varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`email`  varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`firstname`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`lastname`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`birthdate`  date NULL DEFAULT NULL ,
`regdate`  datetime NOT NULL ,
`lastlogin`  datetime NOT NULL ,
`status`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '-2:cancel;0:wait;1:active;2:suspend;3:reject' ,
`verified`  tinyint(4) NOT NULL DEFAULT 0 COMMENT 'b0:email;b1:mobile' ,
`address`  varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`district`  int(11) NOT NULL DEFAULT 0 ,
`province`  int(11) NOT NULL DEFAULT 0 ,
`country`  char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'vn' ,
`passport`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`card_id`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`role`  tinyint(4) NOT NULL DEFAULT 0 COMMENT '0:member;1:tester;2:admin;3:developer' ,
`src_type`  tinyint(4) NULL DEFAULT 0 COMMENT '-1:admin;0:web;1:app;2:facebook;3:google;4:twitter' ,
PRIMARY KEY (`userid`),
UNIQUE INDEX `IX_email` USING BTREE (`email`) ,
INDEX `IX_username` USING BTREE (`username`) ,
INDEX `IX_regdate` USING BTREE (`regdate`) ,
INDEX `IX_status` USING BTREE (`status`) 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1

;

-- ----------------------------
-- Records of users
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Procedure structure for `users_add`
-- ----------------------------
DROP PROCEDURE IF EXISTS `users_add`;

CREATE PROCEDURE `users_add`(ploginid int,pusername varchar(50),ppassword varchar(64),pemail varchar(150),pfirstname varchar(50),plastname varchar(50),pbirthdate date,pgender tinyint(4),pstatus tinyint(4),pcountry char(2),prole tinyint(4))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	DECLARE rUserId INT DEFAULT 0;
	IF rErrCode=0 AND _admin_check_permission(1<<1,ploginid,'user')=0 AND ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode=0 AND _user_exists(pusername)=1 THEN
		SET rErrCode=2;#username is exists
	END IF;
	IF rErrCode=0 AND _user_email_exists(0,pemail)=1 THEN
		SET rErrCode=3;#email is exists
	END IF;
	IF rErrCode = 0 THEN
		INSERT users(username,`password`,email,firstname,lastname,
								 birthdate,regdate,lastlogin,status,country,
								 role,gender								 
								)
		VALUE(pusername, ppassword, pemail, pfirstname, plastname,
				  pbirthdate, NOW(), NOW(), pstatus, pcountry, 
					prole,pgender
		);
		SET rUserId = LAST_INSERT_ID();	
	END IF;
	SELECT rErrCode AS errCode, rUserId AS userid;
END;

-- ----------------------------
-- Procedure structure for `users_get`
-- ----------------------------
DROP PROCEDURE IF EXISTS `users_get`;

CREATE PROCEDURE `users_get`(puserid int(11))
    READS SQL DATA
BEGIN	
		SELECT *
		FROM users
		WHERE userid=puserid;
END;

-- ----------------------------
-- Procedure structure for `users_get_email`
-- ----------------------------
DROP PROCEDURE IF EXISTS `users_get_email`;

CREATE PROCEDURE `users_get_email`(pemail varchar(150))
    READS SQL DATA
BEGIN	
		SELECT *
		FROM users
		WHERE email=pemail;
END;

-- ----------------------------
-- Procedure structure for `users_get_username`
-- ----------------------------
DROP PROCEDURE IF EXISTS `users_get_username`;

CREATE PROCEDURE `users_get_username`(pusername varchar(50))
    READS SQL DATA
BEGIN	
		SELECT *
		FROM users
		WHERE username=pusername;
END;

-- ----------------------------
-- Procedure structure for `users_getall_a`
-- ----------------------------
DROP PROCEDURE IF EXISTS `users_getall_a`;

CREATE PROCEDURE `users_getall_a`(ploginid int,pstatus tinyint,poption tinyint(4)/**0:all;1:fullname;2:email;3:username*/,pkeyword varchar(50),pstart date,pend date,poffset int,plimit smallint(6))
    READS SQL DATA
BEGIN
	#call: 1,-1,0,'tu+khoa',null,null,0,10
	DECLARE rFoundRows INT DEFAULT 0;
	DECLARE rType TINYINT DEFAULT 0;
	DECLARE rStatus TINYINT DEFAULT 0;
	CREATE TEMPORARY TABLE IF NOT EXISTS tmpusers(userid bigint);	
	# permission view
	SET rType = _admin_role(ploginid);
	IF rType < 3 THEN SET rStatus = 3;
	ELSE SET rStatus = rType + 1;	
	END IF;
	SET @sqlStm = CONCAT('
												INSERT tmpusers(userid)
												SELECT SQL_CALC_FOUND_ROWS
														userid
												FROM clubs
												WHERE userid IS NOT NULL',	
													IF(pkeyword !='',
															IF(poption=0,																
																 CONCAT(' AND (
																		CONCAT(`firstname`,'' '',`lastname`) LIKE ''',pkeyword,'%''																		
																			OR
																		`email` LIKE ''',pkeyword,'%''
																			OR
																		`username` LIKE ''',pkeyword,'%''
																	) '),
																 ELT(poption,
																		CONCAT(' AND CONCAT(`firstname`,'' '',`lastname`) LIKE ''',pkeyword,'%'''),
																		CONCAT(' AND email LIKE ''',pkeyword,'%'''),
																		CONCAT(' AND username LIKE ''%',pkeyword,'%''')
																 )
															),
															''
													),
													IF(pstatus > -1, 
														CONCAT(' AND ABS(status)=',pstatus),
														CONCAT(' AND ABS(status)<',rStatus)
													),													
													IF(pstart <> '0000-00-00' AND pend <> '0000-00-00', CONCAT(' AND LEFT(created,10) BETWEEN ''',pstart,''' AND ''',pend,''''),''),
													IF(pstart <> '0000-00-00' AND pend = '0000-00-00', CONCAT(' AND LEFT(created,10)=''',pstart,''''),''),
													IF(pstart = '0000-00-00' AND pend <> '0000-00-00', CONCAT(' AND LEFT(created,10)=''',pend,''''),''),
												' ORDER BY userid DESC
												LIMIT ',poffset,',',plimit
											);

	PREPARE stmt FROM @sqlStm;
	EXECUTE stmt;

	SET rFoundRows = FOUND_ROWS();
	
	SELECT SQL_SMALL_RESULT rFoundRows AS foundRows, B.*
	FROM tmpusers A JOIN clubs B ON A.userid=B.userid;

	DROP TEMPORARY TABLE IF EXISTS tmpusers;	
END;

-- ----------------------------
-- Procedure structure for `users_update`
-- ----------------------------
DROP PROCEDURE IF EXISTS `users_update`;

CREATE PROCEDURE `users_update`(ploginid int,puserid int,pfirstname varchar(50),plastname varchar(50),pbirthdate date,pgender tinyint(4),paddress varchar(150),pdistrict int(11),pprovince int(11),pcountry char(2),ppassport varchar(20),pcard_id varchar(20))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;

	IF rErrCode=0 AND _admin_check_permission(1<<2,ploginid,'user')=0 AND ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN		
		UPDATE users
		SET firstname=pfirstname,	
				lastname=plastname,
				birthdate=pbirthdate,
				gender=pgender,
				address=paddress,
				district=pdistrict,
				province=pprovince,
				country=pcountry,
				passport=ppassport,
				card_id=pcard_id
		WHERE userid=puserid;
	END IF;
	SELECT rErrCode AS errCode;
END;

-- ----------------------------
-- Procedure structure for `users_update_email`
-- ----------------------------
DROP PROCEDURE IF EXISTS `users_update_email`;

CREATE PROCEDURE `users_update_email`(ploginid int,puserid int,pemail varchar(150))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;

	IF rErrCode=0 AND _admin_check_permission(1<<2,ploginid,'user')=0 AND ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode=0 AND _users_email_exists(puserid,pemail)=1 THEN
		SET rErrCode=2;#email is exists
	END IF;
	IF rErrCode = 0 THEN		
		UPDATE users SET `email`=pemail WHERE userid=puserid;
	END IF;
	SELECT rErrCode AS errCode;
END;

-- ----------------------------
-- Procedure structure for `users_update_password`
-- ----------------------------
DROP PROCEDURE IF EXISTS `users_update_password`;

CREATE PROCEDURE `users_update_password`(ploginid int,puserid int,ppassword varchar(64))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;

	IF rErrCode=0 AND _admin_check_permission(1<<2,ploginid,'user')=0 AND ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode = 0 THEN		
		UPDATE users SET `password`=ppassword WHERE userid=puserid;
	END IF;
	SELECT rErrCode AS errCode;
END;

-- ----------------------------
-- Procedure structure for `users_update_status`
-- ----------------------------
DROP PROCEDURE IF EXISTS `users_update_status`;

CREATE PROCEDURE `users_update_status`(ploginid int(11),puserid int(11),pstatus tinyint(4),pperm tinyint(4))
    MODIFIES SQL DATA
BEGIN
	DECLARE rErrCode INT DEFAULT 0;
	IF rErrCode=0 AND _admin_check_permission(pperm,ploginid,'user')=0 AND ploginid > 0 THEN
		SET rErrCode=1;#no permission
	END IF;
	IF rErrCode=0 THEN		
		UPDATE users SET `status`=pstatus WHERE userid=puserid;	
	END IF;
	SELECT rErrCode AS errCode;
END;

-- ----------------------------
-- Function structure for `_users_email_exists`
-- ----------------------------
DROP FUNCTION IF EXISTS `_users_email_exists`;

CREATE FUNCTION `_users_email_exists`(puserid int,pemail varchar(150)) RETURNS int(11)
BEGIN
	IF puserid = 0 THEN
		IF EXISTS(
			 SELECT 1
			 FROM users WHERE email=pemail
			) THEN
			RETURN 1;
		ELSE
			RETURN 0;
		END IF;
	ELSE 
		IF EXISTS (SELECT 1 FROM users WHERE email=pemail AND userid<>puserid LIMIT 1) THEN
			RETURN 1;
		ELSE
			RETURN 0;
		END IF;
	END IF;
END;

-- ----------------------------
-- Function structure for `_users_exists`
-- ----------------------------
DROP FUNCTION IF EXISTS `_users_exists`;

CREATE FUNCTION `_users_exists`(pusername varchar(30)) RETURNS int(11)
BEGIN
	IF EXISTS(
		 SELECT 1
		 FROM users WHERE username=pusername
		) THEN
		RETURN 1;
	ELSE
		RETURN 0;
	END IF;
END;

-- ----------------------------
-- Auto increment value for `users`
-- ----------------------------
ALTER TABLE `users` AUTO_INCREMENT=1;
